# -*- coding: utf-8 -*-
# =============================================================================
#     FileName:
#         Desc:
#       Author:
#        Email:
#     HomePage:
#      Version:
#   LastChange:
#      History:
# =============================================================================
import datetime
import traceback
import logging
import uuid
import simplejson
import mysql.connector
import mysql.connector.pooling


class MySQLClient(object):
    def __init__(self, mysql_host: str, mysql_port: int,
                 mysql_user: str, mysql_password: str,
                 database_name: str = "mysql", connect_timeout: int = 10,
                 mysql_charset: str = "utf8mb4", mysql_collation="utf8mb4_general_ci",
                 logger=None):
        self.mysql_host = mysql_host
        self.mysql_user = mysql_user
        self.mysql_password = mysql_password
        self.mysql_port = mysql_port
        self.connect_timeout = connect_timeout
        self.mysql_charset = mysql_charset
        self.database_name = database_name
        self.mysql_pool_name = str(uuid.uuid4())
        self.mysql_pool = mysql.connector.pooling.MySQLConnectionPool(
            pool_name=self.mysql_pool_name,
            pool_size=5,
            host=self.mysql_host,
            port=self.mysql_port,
            user=self.mysql_user,
            password=self.mysql_password,
            database=self.database_name,
            connect_timeout=self.connect_timeout,
            charset=mysql_charset,
            collation=mysql_collation
        )
        if logger is None:
            logger = logging.getLogger("mysql")
        self.logger = logger

    def _get_connection(self):
        """
        获取当前服务器的MySQL连接
        :return:
        """
        conn = self.mysql_pool.get_connection()
        conn.ping(reconnect=True, attempts=3)
        return conn

    def check_connection(self):
        """
        检查数据库链接是否正常，
        :return: 连接正常则返回True，连接异常则返回False
        """
        try:
            self.mysql_query(sql_script="select 1;")
            return True
        except Exception as _:
            return False

    @classmethod
    def _set_max_execute_seconds(cls, cursor, max_execution_seconds):
        """
        尝试设置会话级别的最大执行超时时间
        :param cursor:
        :param max_execution_seconds:
        :return:
        """
        if max_execution_seconds <= 0:
            return
        try:
            cursor.execute("set max_statement_time={};".format(max_execution_seconds * 1000))
        except Exception as _:
            pass
        try:
            cursor.execute("set max_execution_time={}".format(max_execution_seconds * 1000))
        except Exception as _:
            pass

    def mysql_query(self, sql_script, sql_paras=None, return_dict=False, max_execution_seconds=-1):
        """
        执行查询并返回数据
        :param sql_script:
        :param sql_paras:
        :param return_dict:
        :param max_execution_seconds:
        :return:
        """
        conn = None
        cursor = None
        try:
            query_message = "请求地址: {}:{}\n请求SQL: {}\n请求参数: {}".format(
                self.mysql_host, self.mysql_port,
                sql_script, simplejson.dumps(sql_paras, default=str)
            )
            self.logger.debug(query_message)
            conn = self._get_connection()
            if return_dict:
                cursor = conn.cursor(dictionary=True)
            else:
                cursor = conn.cursor()
            self._set_max_execute_seconds(cursor=cursor, max_execution_seconds=max_execution_seconds)
            if sql_paras is not None:
                cursor.execute(sql_script, sql_paras)
            else:
                cursor.execute(sql_script)
            exec_result = cursor.fetchall()
            conn.commit()
            return exec_result
        except Exception as ex:
            warning_message = (
                "执行SQL出现异常,请求地址: {}:{}\n请求SQL: {}\n"
                "请求参数: {}\n异常信息:{}\n堆栈信息：{}"
            ).format(
                self.mysql_host, self.mysql_port,
                sql_script, simplejson.dumps(sql_paras, default=str),
                str(ex), traceback.format_exc(),
            )
            self.logger.warning(warning_message)
            raise Exception(str(ex))
        finally:
            if cursor is not None:
                cursor.close()
            if conn is not None:
                conn.close()

    def mysql_exec(self, sql_script, sql_paras=None):
        """
        执行传入的脚本，返回影响行数
        :param sql_script:
        :param sql_paras:
        :return: 脚本最后一条语句执行影响行数
        """
        cursor = None
        conn = None
        try:
            query_message = "请求地址: {}:{}\n请求SQL: {}\n请求参数: {}".format(
                self.mysql_host, self.mysql_port,
                sql_script, simplejson.dumps(sql_paras, default=str)
            )
            self.logger.debug(query_message)
            conn = self._get_connection()
            cursor = conn.cursor()
            if sql_paras is not None:
                cursor.execute(sql_script, sql_paras)
            else:
                cursor.execute(sql_script)
            affect_rows = cursor.rowcount
            conn.commit()
            return affect_rows
        except Exception as ex:
            warning_message = (
                "执行SQL出现异常,请求地址: {}:{}\n请求SQL: {}\n"
                "请求参数: {}\n异常信息:{}\n堆栈信息：{}"
            ).format(
                self.mysql_host, self.mysql_port,
                sql_script, simplejson.dumps(sql_paras, default=str),
                str(ex), traceback.format_exc(),
            )
            self.logger.warning(warning_message)
            raise Exception(str(ex))
        finally:
            if cursor is not None:
                cursor.close()
            if conn is not None:
                conn.close()

    def mysql_exec_many(self, exec_items):
        """
        将多个SQL放到一个事务中执行
        :param exec_items:
        :return:
        """
        cursor = None
        conn = None
        total_affect_rows = 0
        try:
            conn = self._get_connection()
            cursor = conn.cursor()
            for sql_script, sql_param in exec_items:
                if sql_param is not None:
                    cursor.execute(sql_script, sql_param)
                else:
                    cursor.execute(sql_script)
                affect_rows = cursor.rowcount
                if affect_rows is not None:
                    total_affect_rows += affect_rows
            conn.commit()
            return total_affect_rows
        except Exception as ex:
            conn.rollback()
            warning_message = "执行出现异常：{}\n堆栈：{}\n参数：{}".format(
                str(ex),
                traceback.format_exc(),
                exec_items
            )
            self.logger.warning(warning_message)
            raise Exception(str(ex))
        finally:
            if cursor is not None:
                cursor.close()
            if conn is not None:
                conn.close()


def demo():
    mysql_client = MySQLClient(
        mysql_host="127.0.0.1", mysql_port=3306,
        mysql_user="root", mysql_password="xxxx"
    )
    sql_script = (
        "SELECT * FROM redis_info.redis_8182 "
        "WHERE key_name = 'CSP_CTRIP_ctri\p_17335682_2023%\s'  "
        "AND key_id = %s "
    )
    sql_paras = [4]
    dt1 = datetime.datetime.now()
    query_rows = mysql_client.mysql_query(sql_script=sql_script, sql_paras=sql_paras, return_dict=True)
    dt2 = datetime.datetime.now()
    print((dt2 - dt1).total_seconds() * 1000)
    print(query_rows)


if __name__ == '__main__':
    demo()
